package cn.swust.service.serviceimpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.swust.model.PoorLibBean;
import cn.swust.service.PoorLibService;
import cn.swust.util.DBUtil;

public class PoorLibServiceImpl implements PoorLibService {

	public int addPoor(PoorLibBean po) {
		int ret = 0;
		try{
		String insertSql = "insert into poorlib(poor_chname,poor_stunum,poor_stucard,poor_level)" +
		" values ('" + po.getChName() + "','" + po.getStuNum() + "','" +
		po.getStuCard() + "'," + po.getLevel() + ")";
		Connection conn = DBUtil.getConnection();
		Statement state = conn.createStatement();
		state.execute(insertSql, Statement.RETURN_GENERATED_KEYS);
		ResultSet rs = state.getGeneratedKeys();
		while(rs.next())
			ret = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return ret;
	}

	public int dePoorByStuNum(String num) {
		String delSql = "delete from poorlib where poor_stunum = '" + "2009288881" + "'";
		return DBUtil.updateSql(delSql);
	}

	public int delPoorById(int id) {
		String delSql = "delete from poorlib where order_id = " + id;
		return DBUtil.updateSql(delSql);
	}

	public int modPoor(String stuNum, PoorLibBean po) {
		String modSql = "update poorlib set poor_chname = '" + po.getChName() +
		"',poor_stunum = '" + po.getStuNum() + "',poor_stucard = '" + po.getStuCard() +
		"',poor_level = " + po.getLevel() + " where poor_stunum = '" + stuNum + "'";
		return DBUtil.updateSql(modSql);
	}

	public PoorLibBean queryById(int id) {
		String insertSql = "select * from poorlib where poor_id = " + id;
		ResultSet rs = DBUtil.querySql(insertSql, DBUtil.creatStatement(DBUtil.getConnection()));
		PoorLibBean po = new PoorLibBean();
		try {
			if (rs.next()) {
				po.setId(rs.getInt("poor_id"));
				po.setChName(rs.getString("poor_chname"));
				po.setStuNum(rs.getString("poor_stunum"));
				po.setStuCard(rs.getString("poor_stucard"));
				po.setLevel(rs.getInt("poor_level"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return po;
	}

	public ArrayList<PoorLibBean> queryByLevel(int level) {
		String insertSql = "select * from poorlib where poor_level = " + level;
		ResultSet rs = DBUtil.querySql(insertSql, DBUtil.creatStatement(DBUtil.getConnection()));
		ArrayList<PoorLibBean> proList = new ArrayList<PoorLibBean>();
		try {
			while (rs.next()) {
				PoorLibBean po = new PoorLibBean();
				po.setId(rs.getInt("poor_id"));
				po.setChName(rs.getString("poor_chname"));
				po.setStuNum(rs.getString("poor_stunum"));
				po.setStuCard(rs.getString("poor_stucard"));
				po.setLevel(rs.getInt("poor_level"));
				proList.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return proList;
	}

	public PoorLibBean queryByNum(String num) {
		String insertSql = "select * from poorlib where poor_stunum = '" + num + "'";
		ResultSet rs = DBUtil.querySql(insertSql, DBUtil.creatStatement(DBUtil.getConnection()));
		PoorLibBean po = new PoorLibBean();
		try {
			if (rs.next()) {
				po.setId(rs.getInt("poor_id"));
				po.setChName(rs.getString("poor_chname"));
				po.setStuNum(rs.getString("poor_stunum"));
				po.setStuCard(rs.getString("poor_stucard"));
				po.setLevel(rs.getInt("poor_level"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return po;
	}

	public int modPoorById(int id, PoorLibBean po) {
		String modSql = "update poorlib set poor_chname = '" + po.getChName() +
		"',poor_stunum = '" + po.getStuNum() + "',poor_stucard = '" + po.getStuCard() +
		"',poor_level = " + po.getLevel() + " where poor_id = " + id;
		return DBUtil.updateSql(modSql);
	}

}
